import requests
import sqlite3
import pandas as pd
import time
import datetime
from bs4 import BeautifulSoup
from comm.tsapis import ts,logger,Tsapi
import multiprocessing
from comm.datefunc import Date
TimeOut = 5
tb_nm = 'stock_info'


def tableCheck(conn):
    curs = conn.cursor()
    sql = "SELECT name num FROM sqlite_master WHERE type='table' AND name='{0}'".format(tb_nm)
    curs.execute(sql)
    isexists = curs.fetchall()
    if len(isexists) > 0:
        curs.close()
    else:
        sql = """CREATE TABLE stock_info (
         stock_cd TEXT,
         sjkzr TEXT,
         get_dt TEXT
         )"""
        curs.execute(sql)
        curs.close()


def get_last_rpt_dt(conn):
    curs = conn.cursor()
    sql = "SELECT stock_cd, max(rpt_dt) rpt_dt FROM {0} group by stock_cd".format(tb_nm)
    curs.execute(sql)
    dates = curs.fetchall()
    if len(dates) > 0:
        return dict(dates)
    else:
        return {'1000': '2020-01-01'}


def to_float(str_num):
    try:
        return float(str_num)
    except Exception:
        return 0;


def sjkzr(stock_code):
    url = f"http://basic.10jqka.com.cn/{stock_code}/company.html" #+ str(stock_code)
    # url = "http://emweb.securities.eastmoney.com/ShareholderResearch/Index?type=web&code=" + str(stock_code)
    #logger.info(url)
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36'};
    result = requests.session().get(url, headers=headers, timeout=TimeOut)  # requests.get(url);
    result.encoding='GBK' #.text.encode('gbk').decode('utf-8') #
    soup = BeautifulSoup(result.text, "html.parser")
    divs = soup.find_all("div",class_='m_tab_content2')
    arr=[]
    for tbs in divs:
        flags=0
        for tb in tbs.find_all("table",attrs={'class':'m_table ggintro managelist'}):
            for tr in tb.find_all("tr"):
                for tds in tr.find_all("td"):
                    # print(tds.text)
                    # tp.append(tds.text)
                    for spans in tds.find_all("span"):
                        flags=flags+1
                        arr.append(spans.text)
    if len(arr) > 3:
        nm=arr[3].split()[0]
        # conn=ts.conn.engine.raw_connection()
        # cur=conn.cursor()
        # cur.execute(f"delete from {tb_nm} where stock_cd='{stock_code}'")
        # cur.execute(f"insert into {tb_nm}(stock_cd,sjkzr,get_dt) values('{stock_code}','{nm}','{Date().batch_dt()}');")
        # cur.execute(f"delete from {tb_nm} where stock_cd='{stock_code}';insert into {tb_nm}(stock_cd,sjkzr,get_dt) values('{stock_code}','{nm}','{Date().batch_dt()}');")
        return nm
        # df.drop(columns='序',inplace=True,errors='ignore')
        # return df
    else:
        return None
    
def get_all():
    ts=Tsapi()
    if 'stocks' not in dir():
        stocks=ts.get_stock_list()
        # 查询当前所有正常上市交易的股票列表
    tableCheck(ts.conn.get_conn_refresh())
    # stock_max_rpt_dt = get_last_rpt_dt(ts.conn.get_conn_refresh())
    total = len(stocks)
    today = datetime.datetime.today().strftime('%Y-%m-%d') 
    i = 0
    rs=[]
    for js in stocks.to_dict('records'):
        i = i + 1
        # if i >20:
        #     break
        code=js['stock_cd']
        nm=sjkzr(code)
        if nm is not None:
            rs.append({'stock_cd':code,'sjkzr':nm,'get_dt':today})
        if(i%100==0):
            logger.info(f"正在获取第{i}个,共：{total} {code}")  
            df=pd.DataFrame(rs)
            df.to_sql(tb_nm, ts.conn.get_conn_refresh(), if_exists='append', index=False)
            rs=[]


if __name__ == '__main__':
    get_all()
    # ts=Tsapi()
    # if 'stocks' not in dir():
    #     stocks=ts.get_stock_list()
    #     # 查询当前所有正常上市交易的股票列表
    # tableCheck(ts.conn.get_conn_refresh())
    # # stock_max_rpt_dt = get_last_rpt_dt(ts.conn.get_conn_refresh())
    # total = len(stocks)
    # today = datetime.datetime.today().strftime('%Y-%m-%d') 
    # i = 0
    # rs=[]
    # logger.info("获取实际控制人 启动进程数: 5，开始批处理")
    # # multiprocessing.freeze_support()
    # # pool = multiprocessing.Pool(10)
    # for js in stocks.to_dict('records'):
    #     i = i + 1
    #     code=js['stock_cd']
    #     if(i%100==0):
    #         logger.info(f"正在获取第{i}个,共：{total} {code}")
    #     sjkzr(ts,code)   
    #     # pool.apply_async(sjkzr, args=({'tsapi':ts,'stock_code':code},))
    # # pool.close()
    # # pool.join()
    # logger.info(" 完成数据导入")
        # nm=sjkzr(code)
        # if nm is not None:
    #     #     rs.append({'stock_cd':code,'sjkzr':nm,'get_dt':today})
    # df=pd.DateFrame(rs)
    # df.to_sql(tb_nm, ts.conn.get_conn_refresh(), if_exists='append', index=False)